package com.css.fxfzfxqh.modules.singleIndexRiskZoning.repository.impl;

import com.css.fxfzfxqh.modules.singleIndexRiskZoning.model.entity.*;
import com.css.fxfzfxqh.modules.singleIndexRiskZoning.param.QhSingleExportParam;
import com.css.fxfzfxqh.modules.singleIndexRiskZoning.repository.EnterAssessQhRepository;
import com.css.fxfzfxqh.modules.singleIndexRiskZoning.vo.GdpVO;
import com.css.fxfzfxqh.modules.singleIndexRiskZoning.vo.QhSingleExportVO;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: zhangSongRui
 * @CreateTime: 2023/07/25
 * @Description: TODO
 */
@Repository
public class EnterAssessQhRepositoryImpl implements EnterAssessQhRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Map<String, Object> getAssessPage(Map<String, Object> param, int curPage, int pageSize) {
        String sql = "select * from fxqh_assess_ed_qh where del_flag='0'";
        String limitSql = " limit " + pageSize + " offset " + (curPage - 1) * pageSize;
        if (PlatformObjectUtils.isNotEmpty((String) param.get("zoningCode"))) {
            sql = sql + " and zoning_code like '%" + param.get("zoningCode") + "%'";
        }
        if (PlatformObjectUtils.isNotEmpty((String) param.get("zoningName"))) {
            sql = sql + " and zoning_name like '%" + param.get("zoningName") + "%'";
        }
        Integer total = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FxqhAssessEdQh.class)).size();
        List<FxqhAssessEdQh> list = jdbcTemplate.query(sql + limitSql, new BeanPropertyRowMapper<>(FxqhAssessEdQh.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<Assess> getAssessByParam(Map<String, Object> param) {

        /**
         * select t1.id id,t1.assess_code assessCode,t1.assess_name assessName,((case when (t1.house_data_flag='0') then '房屋单体数据' else '房屋网格数据' end)||'('||t1.house_data||')') houseData,  (case when t1.seismic_input_mode='0' then '烈度' else (case when t1.seismic_input_mode='1' then '4个概率水准' else '地震影响场' end) end) seismicInputMode,ARRAY_TO_STRING(ARRAY_AGG(COALESCE(t2.city_name||t2.name,t2.city_name)),'、') divisionName,t1.assess_desc assessDesc
         from (select * from fxqh_economic_loss_assess_ed where del_flag='0' and house_data_flag='1' and seismic_input_mode='0') t1
         left join fxqh_assess_toponym t2 on t1.id=t2.assess_id
         where t2.pp_name='辽宁省' and t2.city_name='大连市' and t2.name='中山区'
         group by t1.id,t1.assess_code,t1.assess_name,t1.house_data_flag,t1.house_data,t1.seismic_input_mode,t1.assess_desc
         */
        StringBuilder sql = new StringBuilder("select t1.id id,t1.assess_code assessCode,t1.assess_name assessName,((case when (t1.house_data_flag='0') then '房屋单体数据' else '房屋网格数据' end)||'('||t1.house_data||')') houseData,  (case when t1.seismic_input_mode='0' then '烈度' else (case when t1.seismic_input_mode='1' then '4个概率水准' else '地震影响场' end) end) seismicInputMode,ARRAY_TO_STRING(ARRAY_AGG(COALESCE(t2.city_name||t2.name,t2.city_name)),'、') divisionName,t1.assess_desc assessDesc" +
                " from (select * from ");
        String estimateObj = (String) param.get("estimateObj");
        if ("1".equals(estimateObj)) {
            //抗震能力
            sql.append(" fxqh_resist_earthquakes_assess_ed");
        } else if ("2".equals(estimateObj)) {
            //人员伤亡
            sql.append(" fxqh_casualties_assess_ed");
        } else if ("3".equals(estimateObj)) {
            //经济损失
            sql.append(" fxqh_economic_loss_assess_ed");
        }
        sql.append(" where del_flag='0'");
        //0:房屋单体数据;1:房屋网格数据
        if (PlatformObjectUtils.isNotEmpty((String) param.get("houseDataFlag"))) {
            sql.append(" and house_data_flag='" + param.get("houseDataFlag") + "'");
        }
        //0:烈度;1:4个概率水准;2:地震影响场
        if (PlatformObjectUtils.isNotEmpty((String) param.get("seismicInputMode"))) {
            sql.append(" and seismic_input_mode='" + param.get("seismicInputMode") + "'");
        }
        sql.append(") t1 left join fxqh_assess_toponym t2 on t1.id=t2.assess_id where 1=1 ");
        if (PlatformObjectUtils.isNotEmpty((String) param.get("provinceName"))) {
            if (PlatformObjectUtils.isNotEmpty((String) param.get("cityName"))) {
                if (PlatformObjectUtils.isNotEmpty((String) param.get("countyName"))) {
                    sql.append(" and t2.name='" + param.get("countyName") + "'");
                } else {
                    sql.append(" and t2.city_name='" + param.get("cityName") + "'");
                }
            } else {
                sql.append(" and t2.pp_name='" + param.get("provinceName") + "'");
            }
        }
        sql.append("group by t1.id,t1.assess_code,t1.assess_name,t1.house_data_flag,t1.house_data,t1.seismic_input_mode,t1.assess_desc");
        List<Assess> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Assess.class));
        return list;
    }

    @Override
    public int saveFxqhAssessEd(FxqhAssessEdQh entity) {
        String sql = "insert into fxqh_assess_ed_qh(fae_id,zoning_name,zoning_desc,estimate_obj,assess_codes,condition," +
                "condition_type,zoning_code,division_names,house_data_flag,create_user,create_time,del_flag," +
                "seismic_input_mode,create_user_name,dept_name) " +
                "values(:faeId,:zoningName,:zoningDesc,:estimateObj,:assessCodes,:condition,:conditionType," +
                ":zoningCode,:divisionNames,:houseDataFlag,:createUser,:createTime,:delFlag,:seismicInputMode" +
                ",:createUserName,:deptName)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int saveFxqhAssessEdResult(FxqhAssessEdQhResult entity) {
        String sql = "insert into fxqh_assess_ed_qh_result(id,province,city,county,calculated_value,geom,fae_id" +
                ",borderland_flag,estimate_obj,condition,seismic_input_mode,house_data_flag,task_id,qh_level) " +
                "values(:id,:province,:city,:county,:calculatedValue,st_geometryfromtext(:geom,4490),:faeId,:borderlandFlag" +
                ",:estimateObj,:condition,:seismicInputMode,:houseDataFlag,:taskId,:qhLevel)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public FxqhAssessEdQh getById(String id) {
        String sql = "select * from fxqh_assess_ed_qh where del_flag='0' and fae_id='" + id + "'";
        List<FxqhAssessEdQh> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FxqhAssessEdQh.class));
        return query.size() < 1 ? null : query.get(0);
    }

    @Override
    public List<FxqhAssessEdQhResult> getByFaeId(String faeId) {
        String sql = "select * from fxqh_assess_ed_qh_result where fae_id='" + faeId + "'";
        List<FxqhAssessEdQhResult> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FxqhAssessEdQhResult.class));
        return query;
    }

    @Override
    public int updateFxqhAssessEd(FxqhAssessEdQh entity) {
        String sql = "update fxqh_assess_ed_qh set zoning_name=:zoningName,zoning_desc=:zoningDesc,estimate_obj=:estimateObj," +
                "assess_codes=:assessCodes,condition=:condition,condition_type=:conditionType,zoning_code=:zoningCode," +
                "division_names=:divisionNames,house_data_flag=:houseDataFlag,update_user=:updateUser,update_time=:updateTime," +
                "seismic_input_mode=:seismicInputMode " +
                " where fae_id=:faeId";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        return namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public int delFxqhAssessEdResultByFaeId(String faeId) {
        return jdbcTemplate.update("delete from fxqh_assess_ed_qh_result where fae_id='" + faeId + "'");
    }

    @Override
    public String queryLatest(StringBuilder code) {
        StringBuilder sql = new StringBuilder("select zoning_code from fxqh_assess_ed_qh where del_flag = '0' and zoning_code like '%" + code + "%' order by create_time desc limit 1");
        try {
            return jdbcTemplate.queryForObject(sql.toString(), String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void delByTaskId(String taskId,List<String> typeList) {
        jdbcTemplate.update("delete from fxqh_assess_ed_qh_result where task_id = ? and estimate_obj in ('"+String.join("','", typeList)+"')", taskId);
    }

    @Override
    public List<FxqhAssessEdQhResult> getAssessEdQhList(String taskId) {
        return jdbcTemplate.query("select *,ST_AsText(geom) geomText from fxqh_assess_ed_qh_result where task_id='"+taskId+"' and condition in ('ld1','ld2','ld3','ld4','ld5')",new BeanPropertyRowMapper<>(FxqhAssessEdQhResult.class));
    }

    @Override
    public List<FxqhAssessEdQhResult> getAssessEdQhList(FxqhAssessEdQhResult param, Integer page, Integer rows) {
        StringBuilder sqlBuilder = new StringBuilder("select *,st_astext(geom) geom from fxqh_assess_ed_qh_result where 1=1 ");
        if (ObjectUtils.isNotEmpty(param.getTaskId())) {
            sqlBuilder.append(String.format(" and task_id = '%s' ", param.getTaskId()));
        }
        if (ObjectUtils.isNotEmpty(param.getEstimateObj())) {
            sqlBuilder.append(String.format(" and estimate_obj = '%s' ", param.getEstimateObj()));
        }
        if (ObjectUtils.isNotEmpty(param.getCondition())) {
            sqlBuilder.append(String.format(" and condition = '%s' ", param.getCondition()));
        }
        sqlBuilder.append(" order by province, city, county asc ");
        if (page != null && rows != null) {
            sqlBuilder.append(" limit ").append(Math.max(rows, 1)).append(" offset ").append(rows * (Math.max(page, 1) - 1));
        }
        return jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(FxqhAssessEdQhResult.class));
    }


    @Override
    public List<QhSingleExportVO> getByParam(QhSingleExportParam param) {
        String sql="select province as provinceName,city as cityName,county as countyName,condition,calculated_value " +
                "as calculatedValue,qh_level as qhLevel from fxqh_assess_ed_qh_result where 1=1";
        if (PlatformObjectUtils.isNotEmpty(param.getId())){
            sql=sql+" and task_id='"+param.getId()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())){
            sql=sql+" and province='"+param.getProvince()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())){
            sql=sql+" and city='"+param.getCity()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())){
            sql=sql+" and county='"+param.getCounty()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getAssessObj())){
            sql=sql+" and estimate_obj='"+param.getAssessObj()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getSeismicInputMode())){
            sql=sql+" and seismic_input_mode='"+param.getSeismicInputMode()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCondition())){
            sql=sql+" and condition='"+param.getCondition()+"'";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(QhSingleExportVO.class));
    }


    @Override
    public void save(GdpVO vo) {
        String sql = "insert into fxqh_previous_gdp(id,province_name,city_name,county_name,previous_gdp,task_id,create_time,create_user)" +
                " values (?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,vo.getId(),vo.getProvinceName(),vo.getCityName(),vo.getCountyName(),vo.getPreviousGdp(),vo.getTaskId(),vo.getCreateTime(),vo.getCreateUser());
    }

    @Override
    public void deleteGdpByTaskId(String taskId) {
        jdbcTemplate.update("delete from fxqh_previous_gdp where task_id='"+taskId+"'");
    }

    @Override
    public List<GdpVO> getGdpByTaskId(String taskId) {
        String sql ="select * from fxqh_previous_gdp where task_id='"+taskId+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(GdpVO.class));
    }

    @Override
    public GdpVO getGdpByTaskIdAndDistrict(String taskId, String ppName, String cityName, String name) {
        String sql = "select * from fxqh_previous_gdp where task_id='"+taskId+"' and province_name='"+ppName+"' and city_name='"+cityName+"' and county_name='"+name+"'";
        List<GdpVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(GdpVO.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):null;
    }
}
